﻿using ExcelTools.BaseModels;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.Util;

namespace ExcelTools
{
    public static class ExportWithTemplateExtension
    {

        /// <summary>
        /// 获取标签信息
        /// </summary>
        /// <param name="wb">工作簿对象</param>
        /// <param name="loopInfos">循环标签(输出)</param>
        /// <returns>基本标签</returns>
        public static List<BaseTagInfo> FindTags(this IWorkbook wb, out List<LoopTagInfo> loopInfos)
        {
            var rtnLst = new List<BaseTagInfo>();
            loopInfos = new List<LoopTagInfo>();

            var sheetCnt = wb.NumberOfSheets;
            for (int i = 0; i < sheetCnt; i++)
            {
                var sheet = wb.GetSheetAt(i);

                //约定标签列必须在第一行有标记
                if (sheet.FirstRowNum != 0)
                    continue;

                var headRow = sheet.GetRow(0);
                if (headRow == null)
                    continue;

                var Tags = headRow.Cells.FindAll(x => x.CellType == CellType.String && x.StringCellValue.Contains("#"));

                var loopTagCell = Tags.FirstOrDefault(x => x.StringCellValue.Equals("#loopTagColumn"));
                var baseTagCell = Tags.FirstOrDefault(x => x.StringCellValue.Equals("#baseTagColumn"));

                int loopTagColumnIndex = -1, baseTagColumnIndex = -1;
                if (loopTagCell != null)
                {
                    loopTagColumnIndex = loopTagCell.ColumnIndex;
                    loopTagCell.SetCellValue("");
                }
                if (baseTagCell != null)
                {
                    baseTagColumnIndex = baseTagCell.ColumnIndex;
                    baseTagCell.SetCellValue("");
                }

                FindTagInfo(i, sheet, loopInfos, rtnLst, loopTagColumnIndex, baseTagColumnIndex);
            }

            return rtnLst;
        }

        private static void FindTagInfo(int sheetIndex, ISheet sheet,
            List<LoopTagInfo> loopLst, List<BaseTagInfo> baseLst,
            int loopTagColumnIndex, int baseTagColumnIndex)
        {
            for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
            {
                var tempRow = sheet.GetRow(j);
                //行不存在，直接跳过
                if (tempRow == null)
                    continue;

                FindLoopTagInfo(loopLst, sheetIndex, sheet.SheetName, tempRow, loopTagColumnIndex);
                FindBaseTagInfo(baseLst, sheetIndex, sheet.SheetName, tempRow, baseTagColumnIndex);
            }
        }

        private static void FindLoopTagInfo(List<LoopTagInfo> loopTagLst, int sheetIndex, string sheetName, IRow tempRow, int tagColumnIndex)
        {
            if (tagColumnIndex < 0)
            {
                return;
            }

            var tempCell = tempRow.GetCell(tagColumnIndex);
            if (tempCell != null && tempCell.CellType == CellType.String && tempCell.StringCellValue.Contains("#"))
            {
                var TagInfoArray = tempCell.StringCellValue.Substring(1).Split('_');
                //找到标签就去标签
                tempCell.SetCellValue("");

                if (TagInfoArray.Length <= 1)
                    return;

                if (TagInfoArray[0].ToLower() == "loop" && TagInfoArray.Length == 3)
                {
                    if (TagInfoArray[2].ToLower() == "begin")
                    {

                        loopTagLst.Add(new LoopTagInfo()
                        {
                            SheetIndex = sheetIndex,
                            Key = TagInfoArray[1],
                            StartRowIndex = tempRow.RowNum,
                            EndRowIndex = -1
                        });
                    }
                    else if (TagInfoArray[2].ToLower() == "end")
                    {
                        var TagInfo = loopTagLst.FirstOrDefault(x => x.Key == TagInfoArray[1]);
                        if (TagInfo == null)
                        {
                            throw new Exception($"模板在[{sheetName}]第{tempRow.RowNum + 1}行，出现循环标签未找到起始标签的情况");
                        }
                        TagInfo.EndRowIndex = tempRow.RowNum;
                    }
                }
            }
        }

        private static void FindBaseTagInfo(List<BaseTagInfo> baseTagLst, int sheetIndex, string sheetName, IRow tempRow, int tagColumnIndex)
        {
            if (tagColumnIndex < 0)
            {
                return;
            }

            var tempCell = tempRow.GetCell(tagColumnIndex);
            if (tempCell != null && tempCell.CellType == CellType.String && tempCell.StringCellValue.Contains("#"))
            {
                var TagInfoArray = tempCell.StringCellValue.Substring(1).Split('_');
                //找到标签就去标签
                tempCell.SetCellValue("");

                if (TagInfoArray.Length <= 1)
                    return;

                if (TagInfoArray[0].ToLower() == "table")
                {
                    baseTagLst.Add(new BaseTagInfo()
                    {
                        SheetIndex = sheetIndex,
                        TagColumnIndex = tagColumnIndex,
                        TagType = TagType.Table,
                        DataTableName = TagInfoArray[1],
                        RowIndexs = new int[] { tempRow.RowNum }
                    });
                }
                else if (TagInfoArray[0].ToLower() == "card" && TagInfoArray.Length == 3)
                {
                    if (TagInfoArray[2].ToLower() == "begin")
                    {

                        baseTagLst.Add(new BaseTagInfo()
                        {
                            SheetIndex = sheetIndex,
                            TagColumnIndex = tagColumnIndex,
                            TagType = TagType.Card,
                            DataTableName = TagInfoArray[1],
                            RowIndexs = new int[] { tempRow.RowNum, -1 }
                        });
                    }
                    else if (TagInfoArray[2].ToLower() == "end")
                    {
                        var TagInfo = baseTagLst.FirstOrDefault(x => x.DataTableName == TagInfoArray[1]);
                        if (TagInfo == null)
                        {
                            throw new Exception($"模板在[{sheetName}]第{tempRow.RowNum + 1}行，出现卡片标签未找到起始标签的情况");
                        }
                        TagInfo.RowIndexs[1] = tempRow.RowNum;
                    }
                }
            }
        }



        /// <summary>
        /// 根据标签信息直接进行复制(仅能复制指定标签，建议使用块复制)
        /// </summary>
        /// <param name="wb">工作簿</param>
        /// <param name="TagInfo">标签</param>
        /// <param name="count">复制次数</param>
        /// <returns></returns>
        [Obsolete]
        public static List<BaseTagInfo> CopyTabRange(IWorkbook wb, BaseTagInfo TagInfo, int spaceRowCnt, int count)
        {
            ISheet sheet = wb.GetSheetAt(TagInfo.SheetIndex);

            var rtnLst = new List<BaseTagInfo>();
            int offSetRowNumber = 0;
            for (int i = 0; i < count; i++)
            {
                if (i != 0)
                {
                    if (TagInfo.TagType == TagType.Table)
                    {
                        offSetRowNumber += CopyRange(sheet, TagInfo.RowIndexs[0], TagInfo.RowIndexs[0], spaceRowCnt);
                    }
                    else if (TagInfo.TagType == TagType.Card)
                    {
                        offSetRowNumber += CopyRange(sheet, TagInfo.RowIndexs[0], TagInfo.RowIndexs[1], spaceRowCnt);
                    }
                }

                var temp = new BaseTagInfo()
                {
                    SheetIndex = TagInfo.SheetIndex,
                    TagColumnIndex = TagInfo.TagColumnIndex,
                    TagType = TagInfo.TagType,
                    DataTableName = TagInfo.DataTableName + "-" + i.ToString(),
                };

                if (TagInfo.TagType == TagType.Table)
                {
                    temp.RowIndexs = new int[] { TagInfo.RowIndexs[0] + offSetRowNumber };
                }
                else if (TagInfo.TagType == TagType.Card)
                {
                    temp.RowIndexs = new int[] { TagInfo.RowIndexs[0] + offSetRowNumber, TagInfo.RowIndexs[1] + offSetRowNumber };
                }

                rtnLst.Add(temp);
            }

            return rtnLst;
        }

        /// <summary>
        /// 根据复制标签进行块复制
        /// </summary>
        /// <param name="wb">工作簿</param>
        /// <param name="loopTagInfos">循环标签</param>
        /// <param name="baseTagInfos">基础标签</param>
        public static void CopyRange(this IWorkbook wb, List<LoopTagInfo> loopTagInfos, List<BaseTagInfo> baseTagInfos)
        {
            var loopGroups = loopTagInfos.GroupBy(x => x.SheetIndex);
            foreach (var item in loopGroups)
            {
                var sheet = wb.GetSheetAt(item.Key);
                foreach (var loopTag in item.ToList())
                {
                    var tempTagLst = baseTagInfos.FindAll(x => x.RowIndexs[0] >= loopTag.StartRowIndex
                     && (x.TagType == TagType.Card ? x.RowIndexs[1] : x.RowIndexs[0]) <= loopTag.EndRowIndex);

                    //获取新标签列表
                    var newTagLst = CopyRange(sheet, tempTagLst, loopTag.StartRowIndex, loopTag.EndRowIndex, loopTag.SpaceRowCnt, loopTag.Count);

                    //从基础标签列表中移除被复制的标签
                    baseTagInfos.RemoveAll(x => x.RowIndexs[0] >= loopTag.StartRowIndex
                     && (x.TagType == TagType.Card ? x.RowIndexs[1] : x.RowIndexs[0]) <= loopTag.EndRowIndex);

                    //将复制后的标签添加到基础列表中
                    baseTagInfos.AddRange(newTagLst);
                }
            }
        }

        public static List<BaseTagInfo> CopyRange(ISheet sheet, List<BaseTagInfo> TagInfos, int startRowIndex, int endRowIndex, int spaceRowCnt, int count)
        {

            var rtnLst = new List<BaseTagInfo>();

            int offSetRowNumber = 0;
            for (int i = 0; i < count; i++)
            {
                if (i != 0)
                {
                    offSetRowNumber = CopyRange(sheet, startRowIndex, endRowIndex, spaceRowCnt);
                }

                foreach (var TagInfo in TagInfos)
                {
                    var temp = new BaseTagInfo()
                    {
                        SheetIndex = TagInfo.SheetIndex,
                        TagColumnIndex = TagInfo.TagColumnIndex,
                        TagType = TagInfo.TagType,
                        DataTableName = TagInfo.DataTableName + "-" + i.ToString(),
                    };

                    if (TagInfo.TagType == TagType.Table)
                    {
                        temp.RowIndexs = new int[] { TagInfo.RowIndexs[0] + offSetRowNumber };
                    }
                    else if (TagInfo.TagType == TagType.Card)
                    {
                        temp.RowIndexs = new int[] { TagInfo.RowIndexs[0] + offSetRowNumber, TagInfo.RowIndexs[1] + offSetRowNumber };
                    }

                    rtnLst.Add(temp);
                }
            }

            return rtnLst;
        }

        private static int CopyRange(ISheet sheet, int startRowIndex, int endRowIndex, int spaceRowCnt = 0)
        {
            for (int i = startRowIndex; i <= endRowIndex; i++)
            {
                var targetIndex = sheet.LastRowNum + (i == startRowIndex ? spaceRowCnt : 0) + 1;

                sheet.CopyRow(i, targetIndex);
            }

            return sheet.LastRowNum - endRowIndex;
        }


        /// <summary>
        /// 填充Excel
        /// </summary>
        /// <param name="wb"></param>
        /// <param name="baseTagInfos"></param>
        /// <param name="dataSet"></param>
        public static void FillExcel(this IWorkbook wb, List<BaseTagInfo> baseTagInfos, DataSet dataSet)
        {
            var baseTagGroups = baseTagInfos.GroupBy(x => x.SheetIndex);
            foreach (var item in baseTagGroups)
            {
                var sheet = wb.GetSheetAt(item.Key);
                foreach (var baseTagInfo in item.ToList())
                {
                    DataTable dt = null;
                    if (dataSet != null)
                    {
                        var dtIndex = dataSet.Tables.IndexOf(baseTagInfo.DataTableName);
                        if (dtIndex >= 0)
                        {
                            dt = dataSet.Tables[dtIndex];
                        }
                    }

                    if (baseTagInfo.TagType == TagType.Table)
                    {
                        FillTable(sheet, baseTagInfo, item.ToList(), dt);
                    }
                    else if (baseTagInfo.TagType == TagType.Card)
                    {
                        FillCard(sheet, baseTagInfo, dt);
                    }
                }
            }
        }

        /// <summary>
        /// 填充卡片数据
        /// </summary>
        /// <param name="wb">工作簿</param>
        /// <param name="tagInfo">标签信息</param>
        /// <param name="dt">DataTable</param>
        public static void FillCard(ISheet sheet, BaseTagInfo tagInfo, DataTable dt)
        {
            for (int i = tagInfo.RowIndexs[0]; i <= tagInfo.RowIndexs[1]; i++)
            {
                var tempRow = sheet.GetRow(i);
                if (tempRow != null && tempRow.Cells.Count > 0)
                {
                    var cells = tempRow.Cells.FindAll(x => x.CellType == CellType.String && x.StringCellValue.Contains("#"));
                    foreach (var item in cells)
                    {
                        //dt无数据，直接清除标签即可
                        if (dt == null || dt.Rows.Count <= 0)
                        {
                            item.SetCellValue("");
                        }
                        else
                        {
                            var colIndex = dt.Columns.IndexOf(item.StringCellValue.Trim().Substring(1));
                            if (colIndex >= 0)
                            {
                                var dtCell = dt.Rows[0][colIndex];
                                if (dt.Columns[colIndex].DataType == typeof(double))
                                {
                                    item.SetCellType(CellType.Numeric);
                                    item.SetCellValue(Convert.ToDouble(dtCell));
                                }
                                else
                                {
                                    item.SetCellValue(dtCell.ToString());
                                }
                            }
                            //dt中未找到匹配列，清除标签
                            else
                            {
                                item.SetCellValue("");
                            }
                        }
                    }
                }
            }
        }


        /// <summary>
        /// 填充表格数据
        /// </summary>
        /// <param name="wb"></param>
        /// <param name="TagInfo"></param>
        /// <param name="dt"></param>   
        public static void FillTable(ISheet sheet, BaseTagInfo tagInfo, List<BaseTagInfo> baseTagInfos, DataTable dt)
        {
            //输入数据行数大于1且模板对应行下方有数据时，需要将标签下方插入数据行数减1行
            if (dt != null && dt.Rows.Count > 1 && sheet.LastRowNum > tagInfo.RowIndexs[0])
            {
                //由于ShiftRows会将CopyRow的合并单元格忽视，所以先记录原始合并单元格信息，在ShiftRow之后再加上合并单元格
                //后续，可以研究下源码
                var mergeds = sheet.MergedRegions.FindAll(x => x.FirstRow > tagInfo.RowIndexs[0]);
                foreach (var item in mergeds)
                {
                    sheet.RemoveMergedRegion(sheet.MergedRegions.IndexOf(item));
                }

                sheet.ShiftRows(tagInfo.RowIndexs[0] + 1, sheet.LastRowNum, dt.Rows.Count - 1);

                foreach (var item in mergeds)
                {
                    var newMerged = new CellRangeAddress(
                        item.FirstRow + dt.Rows.Count - 1,
                        item.LastRow + dt.Rows.Count - 1,
                        item.FirstColumn,
                        item.LastColumn
                        );
                    sheet.AddMergedRegion(newMerged);
                }

                baseTagInfos.FindAll(x => x.RowIndexs[0] > tagInfo.RowIndexs[0]).ForEach(x => x.RowIndexs[0] += dt.Rows.Count - 1);
                baseTagInfos.FindAll(x => x.RowIndexs.Length > 1 && x.RowIndexs[1] > tagInfo.RowIndexs[0]).ForEach(x => x.RowIndexs[1] += dt.Rows.Count - 1);
            }


            if (dt == null)
            {
                var tempRow = sheet.GetRow(tagInfo.RowIndexs[0]);
                foreach (var item in tempRow.Cells)
                {
                    if (item.CellType == CellType.String && item.StringCellValue.Contains("#"))
                    {
                        item.SetCellValue("");
                    }
                }

                return;
            }


            //记录标签行信息，包括标签键，列索引，单元格样式
            var templParamLst = sheet.GetRow(tagInfo.RowIndexs[0]).Cells
                .Select(x =>
                {
                    if (x.CellType == CellType.Numeric)
                    {
                        return new
                        {
                            paramKey = x.NumericCellValue.ToString(),
                            x.ColumnIndex,
                            x.CellStyle
                        };
                    }
                    else
                    {
                        return new
                        {
                            paramKey = x.StringCellValue,
                            x.ColumnIndex,
                            x.CellStyle
                        };
                    }
                }).ToList();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow tempRow;
                if (i == 0)
                {
                    tempRow = sheet.GetRow(tagInfo.RowIndexs[0] + i);
                }
                else
                {
                    tempRow = sheet.CreateRow(tagInfo.RowIndexs[0] + i);
                }

                foreach (var item in templParamLst)
                {
                    var tempCell = tempRow.GetCell(item.ColumnIndex);
                    if (tempCell == null)
                    {
                        tempCell = tempRow.CreateCell(item.ColumnIndex);
                    }

                    if (item.paramKey.Trim().Length <= 0)
                        continue;

                    var colIndex = dt.Columns.IndexOf(item.paramKey.Trim().Substring(1));
                    if (colIndex >= 0)
                    {
                        var dtCell = dt.Rows[i][colIndex];
                        if (dt.Columns[colIndex].DataType == typeof(double))
                        {
                            tempCell.SetCellType(CellType.Numeric);
                            tempCell.SetCellValue(Convert.ToDouble(dtCell));
                        }
                        else
                        {
                            tempCell.SetCellValue(dtCell.ToString());
                        }
                        tempCell.CellStyle = item.CellStyle;
                    }
                    //dt中未找到匹配列，清除标签
                    else
                    {
                        tempCell.SetCellValue("");
                    }
                }
            }
        }
    }

}
